﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;

/// <summary>
/// Summary description for SanPhamDAL
/// </summary>
public class SanPhamDAL
{
	public SanPhamDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    
    public DataTable fncG_Get4DataProduct()
    {
        string strSQL="";
        strSQL = "select top 4 *,Substring(dacdiem,0,60+CHARINDEX(' ',Substring(dacdiem,60,DATALENGTH(dacdiem)))) as [GioiThieu]  from SanPham where NhaPhanPhoi=1 order by newid()";
        return DataProvider.GetData(strSQL);
    }
    public DataTable funG_GetAllDataProduct()
    {
        string strSQL = "";
        strSQL = "select * from SanPham a,NhaSanXuat b,NhaPhanPhoi c where a.NhaSanXuat=b.MaNSX and a.NhaPhanPhoi=c.MaNPP order by a.MaSP DESC;";
        return DataProvider.GetData(strSQL);
    }
    public DataTable fnG_GetDetailProduct(SanPhamEntity SanPham)
    {
        string strSQL="";
        strSQL = "select * from SanPham a,NhaSanXuat b,NhaPhanPhoi c where a.MaSP=@masp and a.NhaSanXuat=b.MaNSX and a.NhaPhanPhoi=c.MaNPP;";
        return DataProvider.GetData(strSQL, "@masp", SanPham.MaSP);
    }
    public DataTable fncG_GetProductByNhaPP(NhaPhanPhoiEnum NhaPhanPhoi)
    {
        string strSQL = "";
        strSQL = "select *,Substring(DacDiem,0,60+CHARINDEX(' ',Substring(DacDiem,60,DATALENGTH(DacDiem)))) as [GioiThieu] from SanPham a,NhaPhanPhoi b Where a.NhaPhanPhoi=b.MaNPP and a.NhaPhanPhoi=@NhaPhanPhoi";
        return DataProvider.GetData(strSQL, "@NhaPhanPhoi", (int)NhaPhanPhoi);
    }
    //get max masp product
    public DataTable funG_GetmaxProduct()
    {
        string strSQL = "";
        strSQL = "select top 1 MaSp from SanPham order by MaSP DESC";
        return DataProvider.GetData(strSQL);
    }
    public int AddNew(SanPhamEntity SanPham)
    {
        string sql = "INSERT INTO SanPham VALUES(@MaSP,@TenSP ,@MaLoai,@NhaPhanPhoi,@NhaSanXuat,@HinhAnh,@NgayNhap,"
      +"@DonGia,@MaDVT,@SoLuong ,@DacDiem,@Mota,@AnHien)";
        return DataProvider.ExecuteNonQuery(sql, "@MaSP",SanPham.MaSP,"@TenSP",SanPham.TenSP ,"@MaLoai",SanPham.MaLoai,"@NhaPhanPhoi",SanPham.NhaPhanPhoi,
            "@NhaSanXuat", SanPham.NhaSanXuat, "@HinhAnh", SanPham.HinhAnh, "@NgayNhap", SanPham.NgayNhap, "@DonGia",SanPham.DonGia,"@MaDVT",SanPham.MaDVT,
            "@SoLuong",SanPham.SoLuong,"@DacDiem",SanPham.DacDiem,"@Mota",SanPham.Mota,"@AnHien",SanPham.AnHien);
    }
    public int DeleteProduct(SanPhamEntity SanPham)
    {
        string sql = "delete from sanpham where masp=@masp";
        return DataProvider.ExecuteNonQuery(sql, "@masp", SanPham.MaSP);
    }
    public int UpdateProduct(SanPhamEntity SanPham)
    {
        string strSQL = "";
        strSQL = "Update SanPham ";
        strSQL+= "Set TenSp=@TenSP ,MaLoai=@MaLoai,NhaPhanPhoi=@NhaPhanPhoi,NhaSanXuat=@NhaSanXuat,HinhAnh=@HinhAnh,NgayNhap=@NgayNhap,";
        strSQL+="DonGia=@DonGia,MaDVT=@MaDVT,SoLuong=@SoLuong ,DacDiem=@DacDiem,Mota=@Mota,AnHien=@AnHien";
        strSQL+=" where MaSP=@MaSP";
        return DataProvider.ExecuteNonQuery(strSQL, "@MaSP",SanPham.MaSP,"@TenSP",SanPham.TenSP ,"@MaLoai",SanPham.MaLoai,"@NhaPhanPhoi",SanPham.NhaPhanPhoi,
            "@NhaSanXuat", SanPham.NhaSanXuat, "@HinhAnh",SanPham.HinhAnh, "@NgayNhap", SanPham.NgayNhap, "@DonGia", SanPham.DonGia, "@MaDVT", SanPham.MaDVT,
            "@SoLuong",SanPham.SoLuong,"@DacDiem",SanPham.DacDiem,"@Mota",SanPham.Mota,"@AnHien",SanPham.AnHien);
    }
    /// <summary>
    /// tim kiem san pham
    /// </summary>
    /// <param name="timkiem"></param>
    /// <returns></returns>
    public DataTable fnG_TimKiemSanpham(string timkiem)
    {
        string sql = "select * from SanPham a,NhaPhanPhoi b where a.NhaPhanPhoi=b.MaNPP and (select dbo.funcNVarchar(a.TenSP)) like (select dbo.funcNVarchar(@timkiem));";
        return DataProvider.GetData(sql, "@timkiem", timkiem);
    }
}